﻿using System.Collections.Generic;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using System.Data;
using System;
using System.Text;

namespace Excel
{
    class ExcelHelper
    {

        public ExcelHelper() { }

        /// <summary>
        /// 文件流初始化对象
        /// </summary>
        /// <param name="stream"></param>
        public ExcelHelper(Stream stream)
        {
            _IWorkbook = CreateWorkbook(stream);
        }


        /// <summary>
        /// 传入文件名
        /// </summary>
        /// <param name="fileName"></param>
        public ExcelHelper(string fileName)
        {
            using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                _IWorkbook = CreateWorkbook(fileStream);
            }
        }

        public IWorkbook GetWork
        {
            get
            {
                return _IWorkbook;
            }
        }
        /// <summary>
        /// 工作薄
        /// </summary>
        private IWorkbook _IWorkbook;

        public int SheetNumber
        {
            get
            {
                return _IWorkbook.NumberOfSheets;
            }
        }

        public string GetSheetName(int index)
        {
            return _IWorkbook.GetSheetName(index);
        }

        public ISheet GetSheet(string name)
        {
            return _IWorkbook.GetSheet(name);
        }
        public ISheet GetSheet(int index)
        {
            return _IWorkbook.GetSheetAt(index);
        }

        /// <summary>
        /// 创建工作簿对象
        /// </summary>
        /// <param name="stream"></param>
        /// <returns></returns>
        private IWorkbook CreateWorkbook(Stream stream)
        {
            try
            {
                return new NPOI.XSSF.UserModel.XSSFWorkbook(stream); //07
            }
            catch (Exception)
            {
                try
                {
                    return new HSSFWorkbook(stream); //03
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }

        public string[] ExportToCVSData(ISheet sheet)
        {

            List<string> lines = new List<string>();

            var len = sheet.LastRowNum + 1;

            int colLen = 0;
            for (int row = 0; row < len; row++)
            {
                var col = sheet.GetRow(row);
                if (col.LastCellNum > colLen)
                    colLen = col.LastCellNum;
            }

            for (int row = 0; row < len; row++)
            {
                StringBuilder bui = new StringBuilder();

                var rowData = sheet.GetRow(row);
                for (int col = 0; col < colLen; col++)
                {
                    object data = null;
                    if (col >= rowData.LastCellNum)
                    {
                        data = string.Empty;
                    }
                    else
                    {
                        ICell cell = rowData.GetCell(col);
                        if (cell != null)
                        {
                            try
                            {
                                switch (cell.CellType)
                                {
                                    case CellType.String:
                                        data = cell.StringCellValue;
                                        break;
                                    case CellType.Numeric:
                                        data = cell.NumericCellValue;
                                        break;
                                    case CellType.Boolean:
                                        data = cell.BooleanCellValue;
                                        break;
                                    case CellType.Blank:
                                        data = string.Empty;
                                        break;
                                    default:
                                        data = "ERROR";
                                        break;
                                }
                            }
                            catch
                            {
                                throw new Exception("错误列数：" + data + " " + cell.CellType);
                            }
                        }
                    }

                    bui.Append(data);
                    if (col < colLen - 1)
                        bui.Append(",");
                }

                lines.Add(bui.ToString());
            }

            return lines.ToArray();
        }

        public static int FieldRow = 0;

        /// <summary>
        /// 把Sheet中的数据转换为DataTable
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        public DataTable ExportToDataTable(ISheet sheet)
        {
            if (sheet == null)
                throw new Exception("Sheet 未找到");




            DataTable dt = new DataTable();
            //默认，第一行是字段
            IRow headRow = sheet.GetRow(FieldRow);

            //设置datatable字段
            for (int i = headRow.FirstCellNum; i < headRow.LastCellNum; i++)
            {
                if (i >= headRow.Cells.Count)
                {
                    Console.WriteLine(headRow.Cells[headRow.Cells.Count - 1]);
                    continue;
                }
                string s = GetCellValue(headRow.Cells[i]);
                if (dt.Columns.Contains(s))
                {
                    Console.WriteLine(sheet.SheetName + " 重复包含字段：" + s);
                }
                else
                    dt.Columns.Add(s);
            }

            //遍历数据行
            for (int i = 1, len = sheet.LastRowNum + 1; i < len; i++)
            {
                IRow tempRow = sheet.GetRow(i);
                if (tempRow == null)
                    continue;

                DataRow dataRow = dt.NewRow();
                //遍历一行的每一个单元格
                for (int r = 0, j = tempRow.FirstCellNum, len2 = tempRow.LastCellNum; j < len2; j++, r++)
                {
                    ICell cell = tempRow.GetCell(j);
                    if (cell != null)
                    {
                        try
                        {
                            switch (cell.CellType)
                            {
                                case CellType.String:
                                    dataRow[r] = cell.StringCellValue;
                                    break;
                                case CellType.Numeric:
                                    dataRow[r] = cell.NumericCellValue;
                                    break;
                                case CellType.Boolean:
                                    dataRow[r] = cell.BooleanCellValue;
                                    break;
                                default:
                                    dataRow[r] = "ERROR";
                                    break;
                            }
                        }
                        catch
                        {
                            throw new Exception("错误列数：" + r + " " + cell.CellType);
                        }
                    }
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }

        /// <summary>
        /// Sheet中的数据转换为List集合
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        private IList<T> ExportToList<T>(ISheet sheet, string[] fields) where T : class, new()
        {
            IList<T> list = new List<T>();

            //遍历每一行数据
            for (int i = sheet.FirstRowNum + 1, len = sheet.LastRowNum + 1; i < len; i++)
            {
                T t = new T();
                IRow row = sheet.GetRow(i);

                for (int j = 0, len2 = fields.Length; j < len2; j++)
                {
                    ICell cell = row.GetCell(j);
                    object cellValue = null;

                    switch (cell.CellType)
                    {
                        case CellType.String: //文本
                            cellValue = cell.StringCellValue;
                            break;
                        case CellType.Numeric: //数值
                            cellValue = Convert.ToInt32(cell.NumericCellValue);//Double转换为int
                            break;
                        case CellType.Boolean: //bool
                            cellValue = cell.BooleanCellValue;
                            break;
                        case CellType.Blank: //空白
                            cellValue = "";
                            break;
                        default:
                            cellValue = "ERROR";
                            break;
                    }

                    typeof(T).GetProperty(fields[j]).SetValue(t, cellValue, null);
                }
                list.Add(t);
            }

            return list;
        }

        /// <summary>
        /// 获取第一个Sheet的第X行，第Y列的值。起始点为1
        /// </summary>
        /// <param name="X">行</param>
        /// <param name="Y">列</param>
        /// <returns></returns>
        public string GetCellValue(int X, int Y)
        {
            ISheet sheet = _IWorkbook.GetSheetAt(0);

            IRow row = sheet.GetRow(X - 1);

            return row.GetCell(Y - 1).ToString();
        }

        /// <summary>
        /// 获取一行的所有数据
        /// </summary>
        /// <param name="X">第x行</param>
        /// <returns></returns>
        public string[] GetCells(int X)
        {
            List<string> list = new List<string>();

            ISheet sheet = _IWorkbook.GetSheetAt(0);

            IRow row = sheet.GetRow(X - 1);

            for (int i = 0, len = row.LastCellNum; i < len; i++)
            {
                list.Add(row.GetCell(i).StringCellValue);//这里没有考虑数据格式转换，会出现bug
            }
            return list.ToArray();
        }

        /// <summary>
        /// 第一个Sheet数据，转换为DataTable
        /// </summary>
        /// <returns></returns>
        public DataTable ExportExcelToDataTable()
        {
            return ExportToDataTable(_IWorkbook.GetSheetAt(0));
        }

        /// <summary>
        /// 第sheetIndex表数据，转换为DataTable
        /// </summary>
        /// <param name="sheetIndex">第几个Sheet，从1开始</param>
        /// <returns></returns>
        public DataTable ExportExcelToDataTable(int sheetIndex)
        {
            return ExportToDataTable(_IWorkbook.GetSheetAt(sheetIndex - 1));
        }


        /// <summary>
        /// Excel中默认第一张Sheet导出到集合
        /// </summary>
        /// <param name="fields">Excel各个列，依次要转换成为的对象字段名称</param>
        /// <returns></returns>
        public IList<T> ExcelToList<T>(string[] fields) where T : class, new()
        {
            return ExportToList<T>(_IWorkbook.GetSheetAt(0), fields);
        }

        /// <summary>
        /// Excel中指定的Sheet导出到集合
        /// </summary>
        /// <param name="sheetIndex">第几张Sheet,从1开始</param>
        /// <param name="fields">Excel各个列，依次要转换成为的对象字段名称</param>
        /// <returns></returns>
        public IList<T> ExcelToList<T>(int sheetIndex, string[] fields) where T : class, new()
        {
            return ExportToList<T>(_IWorkbook.GetSheetAt(sheetIndex - 1), fields);
        }


        public string GetCellValue(ICell cell)
        {
            switch (cell.CellType)
            {
                case CellType.String:
                    return cell.StringCellValue.Trim();
                case CellType.Numeric:
                    return cell.NumericCellValue.ToString();
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                default:
                    return "ERROR";
            }
        }

        public void Close()
        {
            try
            {
                _IWorkbook.Close();
            }
            catch (Exception)
            {

            }
        }

    }
}
